package com.sp2p.dao.admin;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import org.apache.commons.lang.StringEscapeUtils;
import org.apache.commons.lang.StringUtils;

import com.shove.data.DataException;
import com.shove.data.DataSet;
import com.shove.data.dao.MySQL;
import com.shove.util.BeanMapUtils;
import com.sp2p.constants.IConstants;
import com.sp2p.database.Dao;
import com.sp2p.database.Dao.Tables;
import com.sp2p.database.Dao.Tables.t_borrow;
import com.sp2p.database.Dao.Tables.t_repayment;
import com.sp2p.database.Dao.Tables.t_repayment_record;

public class FIManageDao {

	public Map<String,String> queryOneFirstChargeDetails(Connection conn, long rechargeId) throws SQLException, DataException{
		Dao.Views.v_t_user_rechargefirst_lists t_recharge_detail = 
			new Dao().new Views().new v_t_user_rechargefirst_lists();
		DataSet ds = t_recharge_detail.open(conn, "*", "id="+rechargeId, "", -1, -1);
		return BeanMapUtils.dataSetToMap(ds);
	}
	
	public Map<String,String> queryOneChargeDetails(Connection conn, long rechargeId) throws SQLException, DataException{
		Dao.Views.v_t_user_rechargedetails_list t_recharge_detail = 
			new Dao().new Views().new v_t_user_rechargedetails_list();
		DataSet ds = t_recharge_detail.open(conn, "*", "id="+rechargeId, "", -1, -1);
		return BeanMapUtils.dataSetToMap(ds);
	}
	
	public Map<String,String> queryOneWithdraw(Connection conn, long wid) throws SQLException, DataException{
		Dao.Views.v_t_user_withdraw_lists t_list = 
			new Dao().new Views().new v_t_user_withdraw_lists();
		DataSet ds = t_list.open(conn, "*", "id="+wid, "", -1, -1);
		return BeanMapUtils.dataSetToMap(ds);
	}
	
	public Map<String,String> queryGongfuAward(Connection conn, long userId) throws SQLException, DataException{
		Dao.Views.v_t_gongfuaward_list t_list = 
			new Dao().new Views().new v_t_gongfuaward_list();
		DataSet ds = t_list.open(conn, "*", "userId="+userId, "", -1, -1);
		return BeanMapUtils.dataSetToMap(ds);
	}
	/**
	 * 后台 进行充值扣费信息添加处理
	 * @param conn
	 * @param paramMap
	 * @return
	 * @throws SQLException
	 */
	public long addBackR_W(Connection conn, Long userId,Long adminId,
			Integer type,double money,String remark,Date date) throws SQLException {
		Dao.Tables.t_recharge_withdraw_info t_info = new Dao().new Tables().new t_recharge_withdraw_info();
		t_info.userId.setValue(userId);
		t_info.type.setValue(type);
		t_info.dealMoney.setValue(money);
		t_info.remark.setValue(remark);
		t_info.checkUser.setValue(adminId);
		t_info.checkTime.setValue(date);
		return t_info.insert(conn);
		
	}
	
	/**
	 * 更新资金流动信息表
	 * @param conn
	 * @param userId
	 * @param handleSum
	 * @param usableSum
	 * @return
	 * @throws SQLException 
	 */
	public Long updateFundrecord(Connection conn,Long userId,double money,int type) throws SQLException{
		if(type == IConstants.WITHDRAW){//扣费
			return MySQL.executeNonQuery(conn,
					" update t_user set `usableSum` = usableSum-" + money
							+" where id=" + userId);
		}else if(type == IConstants.RECHARAGE){//充值
			return MySQL.executeNonQuery(conn,
					" update t_user set `usableSum` = usableSum+" + money
							+" where id=" + userId);
		}else if(type == IConstants.WITHDRAW_FAIL){//审核或者转账失败（将提现的金额变成可用余额）
			return MySQL.executeNonQuery(conn,
					" update t_user set `usableSum` = usableSum+" + money
							+", `freezeSum`=freezeSum-"+money+ " where id=" + userId);
		}else if(type == 100){//转账成功（将冻结余额扣除）IConstants.WITHDRAW_SUCCESS = IConstants.WITHDRAW
			return MySQL.executeNonQuery(conn,
					" update t_user set  `freezeSum`=freezeSum-"+money+ " where id=" + userId);
		}
		else{
			return -1L;
		}
	}
	
	/**
	 * 某用户充值成功总额
	 * @param conn
	 * @param userId
	 * @param result
	 * @return
	 * @throws SQLException
	 * @throws DataException
	 */
	public Map<String,String> querySuccessRecharge(Connection conn,Long userId,int result) 
		throws SQLException, DataException{
		String command = "SELECT sum(rechargeMoney) as r_total from t_recharge_detail where userId="
			+userId+" and result="+result;
		DataSet dataSet = MySQL.executeQuery(conn, command);
		command=null;
		return BeanMapUtils.dataSetToMap(dataSet);
	}
	
	/**
	 * 某用户提现成功总额
	 * @param conn
	 * @param userId
	 * @param result
	 * @return
	 * @throws SQLException
	 * @throws DataException
	 */
	public Map<String,String> querySuccessWithdraw(Connection conn,Long userId,int result) 
	throws SQLException, DataException{
		String command = "SELECT sum(sum) as w_total from t_withdraw where userId="
			+userId+" and status="+result;
		DataSet dataSet = MySQL.executeQuery(conn, command);
		command=null;
		return BeanMapUtils.dataSetToMap(dataSet);
	}
	
	/**
	 * 投标成功总额
	 * @throws SQLException 
	 */
	public Map<String,String> querySuccessBid(Connection conn,Long userId) 
	throws SQLException, DataException{
		Dao.Views.v_t_user_successtotalbid_lists lists = new Dao().new Views().new 
		v_t_user_successtotalbid_lists();
		DataSet dataSet = lists.open(conn, "*", "investor="+userId, "", -1, -1);
		return BeanMapUtils.dataSetToMap(dataSet);
	}
	
	//查询状态，如果已经转账过了，就不再转账
	public Map<String,String> queryTransStatus(Connection conn,Long wid) throws SQLException, DataException{
		Dao.Tables.t_withdraw t_info = new Dao().new Tables().new t_withdraw();
		DataSet dataSet = t_info.open(conn, "*", "id="+wid, "", -1, -1);
		return BeanMapUtils.dataSetToMap(dataSet);
	}
	
	public Long updateWithdraw(Connection conn,Long wid,double money,float poundage,int status,String remark,
			Long adminId,int oldStatus) throws SQLException{
		Dao.Tables.t_withdraw t_info = new Dao().new Tables().new t_withdraw();
		if(status >= 0)
			t_info.status.setValue(status);
		if(money >= 0)
			t_info.sum.setValue(money);
		if(remark != null)
			t_info.remark.setValue(remark);
		if(poundage >=0)
			t_info.poundage.setValue(poundage);
		if(adminId != -100)
			t_info.checkId.setValue(adminId);
		t_info.checkTime.setValue(new Date());
		return t_info.update(conn, " id="+wid+" and status="+oldStatus);
	}
	
	
	public Long updateWithdraws(Connection conn,String wids,int status,Long adminId) throws SQLException{
		String idStr = StringEscapeUtils.escapeSql("'"+wids+"'");
		String idSQL = "-2";
		idStr = idStr.replaceAll("'", "");
		String [] array = idStr.split(",");
		for(int n=0;n<=array.length-1;n++){
			idSQL += ","+array[n];
		}
		Dao.Tables.t_withdraw t_info = new Dao().new Tables().new t_withdraw();
		
		t_info.checkId.setValue(adminId);
		t_info.status.setValue(status);
		t_info.remark.setValue("审核通过");
		t_info.applyTime.setValue(new Date());
		return t_info.update(conn, " id in("+idSQL+")");
	}
	
	public Long updateWithdrawRemark(Connection conn,String wid,String remark) throws SQLException{
		Dao.Tables.t_withdraw t_info = new Dao().new Tables().new t_withdraw();
		t_info.remark.setValue(remark);
		return t_info.update(conn, " id = "+wid);
	}
	
	public Map<String,String> queryR_WInfo(Connection conn,Long rwId) 
	throws SQLException, DataException{
		Dao.Views.v_t_user_backrw_lists t_list = new Dao().new Views().new v_t_user_backrw_lists();
		
		DataSet ds = t_list.open(conn, "*", "id="+rwId, "", -1, -1);
		return BeanMapUtils.dataSetToMap(ds);
	} 
	
	public Map<String,String> queryDueInSum(Connection conn, Long userId) throws SQLException, DataException{
		StringBuffer command = new StringBuffer();
		command.append("select a.usableSum as usableSum ,a.freezeSum as freezeSum,sum(b.recivedPrincipal+b.recievedInterest-b.hasPrincipal-b.hasInterest) forPI");
		command.append(" from t_user a left join t_invest b on a.id = b.investor where a.id="+userId+" group by a.id");
		DataSet dataSet = MySQL.executeQuery(conn, command.toString());
		command=null;
		return BeanMapUtils.dataSetToMap(dataSet);

	}
	
	public List<Map<String,Object>> queryLastRecord(Connection conn) throws SQLException, DataException{
		StringBuffer command = new StringBuffer();
		command.append("SELECT * from (select * from t_fundrecord  ORDER BY recordTime desc ) a GROUP BY userId");
		DataSet dataSet = MySQL.executeQuery(conn, command.toString());
		dataSet.tables.get(0).rows.genRowsMap();
		command=null;
		return dataSet.tables.get(0).rows.rowsMap;

	}
	
	public Long updateRepaymentById(Connection conn, String repaymentId) throws SQLException {
		String idStr = StringEscapeUtils.escapeSql(repaymentId);
		Dao.Tables.t_repayment t_repayment = new Dao().new Tables().new t_repayment();
		t_repayment.repayStatus.setValue(2);
		t_repayment.realRepayDate.setValue(new Date());
		return t_repayment.update(conn, " id in  (" + idStr + ")");
	}

	public Long updateBorrowById(Connection conn, String borrowId, String hasDeadline) throws SQLException {
		String[] bid = borrowId.split(",");
		String[] hasline = hasDeadline.split(",");
		Dao.Tables.t_borrow t_borrow;

		for (int i = 0, length = hasline.length; i < length; i++) {
			String idStr = StringEscapeUtils.escapeSql(bid[i]);
			String lineStr = StringEscapeUtils.escapeSql(hasline[i]);
			t_borrow = new Dao().new Tables().new t_borrow();
			t_borrow.hasDeadline.setValue(Integer.parseInt(lineStr) + 1);
			
			Statement stmt=conn.createStatement();
			stmt.setMaxRows(100);//可以控制输出记录最大数量;
			ResultSet resultSet=stmt.executeQuery("select count(*) as rowCount from t_repayment where borrowId="+idStr+" and repayStatus=1 ");
			resultSet.next();            
			int rowCount = resultSet.getInt("rowCount");
			if(rowCount<=0){
				t_borrow.borrowStatus.setValue(5);
			}
			t_borrow.update(conn, " id=" + idStr);
		}
		return 1L;
	}

	public Long insertRepaymentRecord(Connection conn, String repaymentId, String repayAmount, Long adminId, String addIP) throws SQLException {
		String[] repayid = repaymentId.split(",");
		String[] repayMount = repayAmount.split(",");
		Date now = new Date();
		Dao.Tables.t_repayment_record t_repayment_record;
		for (int i = 0, length = repayMount.length; i < length; i++) {
			String repayidStr = StringEscapeUtils.escapeSql(repayid[i]);
			String amountStr = StringEscapeUtils.escapeSql(repayMount[i]);
			t_repayment_record = new Dao().new Tables().new t_repayment_record();
			t_repayment_record.repayId.setValue(Long.parseLong(repayidStr));
			t_repayment_record.repayAmount.setValue(Double.parseDouble(amountStr));
			//扣除借款人
			updateFundrecord(conn, adminId, Double.parseDouble(amountStr), IConstants.WITHDRAW);
			//偿还投资人
			updateFundrecord(conn, adminId, Double.parseDouble(amountStr), IConstants.RECHARAGE);
			t_repayment_record.oporator.setValue(adminId == null ? -1 : adminId);
			//t_repayment_record.repayType.setValue("手动还款");
			t_repayment_record.createTime.setValue(now);
			//t_repayment_record.oporatorIp.setValue(addIP);
			t_repayment_record.insert(conn);
		}
		return 1L;
	}
	
	public Long insertAllinWithdraw(Connection conn, String reqSn, String wids) throws SQLException {
		Date now = new Date();
		Dao.Tables.t_allin_withdraw t_allin_withdraw;
		String[] widArr=wids.split(",");
		for (int i = 0, length = widArr.length; i < length; i++) {
			t_allin_withdraw = new Dao().new Tables().new t_allin_withdraw();
			t_allin_withdraw.reqSn.setValue(reqSn);
			t_allin_withdraw.wid.setValue(widArr[i]);
			t_allin_withdraw.status.setValue(1);
			t_allin_withdraw.subTime.setValue(now);
			t_allin_withdraw.insert(conn);

		}
		return 1L;
	}
	public Long insertTenWithdraw(Connection conn, String reqSn, String wids) throws SQLException {
		Date now = new Date();
		Dao.Tables.t_ten_withdraw t_ten_withdraw;
		String[] widArr=wids.split(",");
		for (int i = 0, length = widArr.length; i < length; i++) {
			t_ten_withdraw = new Dao().new Tables().new t_ten_withdraw();
			t_ten_withdraw.reqSn.setValue(reqSn);
			t_ten_withdraw.wid.setValue(widArr[i]);
			t_ten_withdraw.status.setValue(1);
			t_ten_withdraw.subTime.setValue(now);
			t_ten_withdraw.insert(conn);

		}
		return 1L;
	}
	public Long insertTenWithdrawComplete(Connection conn, String reqSn, String wids) throws SQLException {
		Date now = new Date();
		Dao.Tables.t_ten_withdraw t_ten_withdraw;
		String[] widArr=wids.split(",");
		for (int i = 0, length = widArr.length; i < length; i++) {
			t_ten_withdraw = new Dao().new Tables().new t_ten_withdraw();
			t_ten_withdraw.reqSn.setValue(reqSn);
			t_ten_withdraw.wid.setValue(widArr[i]);
			t_ten_withdraw.status.setValue(2);
			t_ten_withdraw.subTime.setValue(now);
			t_ten_withdraw.insert(conn);

		}
		return 1L;
	}

	public Map<String, String> queryUserCashSumList(Connection conn,
			String userName, String realName, Double usableSumStart,
			Double usableSumEnd)  throws DataException {
		// TODO Auto-generated method stub
		StringBuffer sql = new StringBuffer();
		String resultFeilds = " sum(IFNULL(usableSum+freezeSum+dueinSum,0)) as totalMoneySum , sum(usableSum) as totalUsableSum, sum(freezeSum) as totalFreezeSum, sum(dueinSum) as totalDueinSum, sum(dueoutSum) as totalUueoutSum ";
		sql.append("select "+ resultFeilds +"from ");
		sql.append("(select a.id as userId,a.username,IFNULL(f.forRePaySum,0) as dueoutSum,a.usableSum,a.freezeSum,");
		sql.append("round(sum(IFNULL(b.recivedPrincipal+b.recievedInterest-b.hasPrincipal-b.hasInterest,0)),2) as dueinSum,d.realName realName from t_user a left join t_invest b on a.id = b.investor ");
		sql.append(" left join t_person d on d.userId=a.id left join ");
		sql.append("(select forRePaySum,publisher from (select sum(IFNULL((c.stillPrincipal+c.stillInterest-c.hasPI+c.lateFI-c.hasFI),0)) as forRePaySum,d.publisher  from t_repayment c left join t_borrow d on c.borrowId = d.id where c.repayStatus = 1 GROUP BY d.publisher) t) f");
		sql.append(" on f.publisher = a.id  group by a.ID,a.usableSum,a.freezeSum,f.forRePaySum,d.realName,a.username) u");
		String command = "";
		if (userName != null) {
			command += " and username like '%"
					+ StringEscapeUtils.escapeSql(userName.trim()) + "%'";
		}
		if (realName != null) {
			command += " and realName like '%"
					+ StringEscapeUtils.escapeSql(realName.trim()) + "%'";
		}
		if (usableSumStart > 0){
			command += " and usableSum >= "+usableSumStart;
		}
		if (usableSumEnd > 0){
			command += " and usableSum <= "+usableSumEnd;
		}
		sql.append(" where 1=1 "+command);
		DataSet dataSet = MySQL.executeQuery(conn, sql.toString());
		return BeanMapUtils.dataSetToMap(dataSet);
	}

	public Map<String, String> queryAllWithdrawSumList(Connection conn,
			String userName, String startTime, String endTime, Integer status) throws DataException{
		// TODO Auto-generated method stub
		String command = "";
		if (StringUtils.isNotBlank(userName)) {
			command += " and name like '%"
					+ StringEscapeUtils.escapeSql(userName) + "%' ";
		}
		if (status > 0) {//
			command += " and status =" + status;
		}
		if (StringUtils.isNotBlank(startTime)) {
			command += " and applyTime >='"
					+ StringEscapeUtils.escapeSql(startTime) + "'";
		}
		if (StringUtils.isNotBlank(endTime)) {
			command += " and applyTime <='"
					+ StringEscapeUtils.escapeSql(endTime) + "'";
		}
		String sql = "select sum(sum) as sumTotal,sum(realMoney) as realMoneyTotal,sum(poundage) as poundageTotal "
			+"from  v_t_user_withdraw_lists "
			+"where 1=1 "+command.toString();
		DataSet dataSet = MySQL.executeQuery(conn, sql);
		return BeanMapUtils.dataSetToMap(dataSet);
	}
}
